package com.anan.builder;

import com.anan.bean.Constants;
import com.anan.bean.TableInfo;
import com.anan.utils.StringTools;
import com.anan.bean.FieldInfo;

import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class GetTableInfo {
    private static final Logger logger = LoggerFactory.getLogger(GetTableInfo.class);
    private static Connection connection = null;

    /**
     * 获取数据库中所有的数据表信息
     *
     * @return
     */
    public static List<TableInfo> getTables(String databaseName) {  //
        try {
            Class.forName(Constants.DB_DRIVER_NAME);
            connection = DriverManager.getConnection(
                    String.format(Constants.DB_URL, databaseName) + Constants.DB_URL_CONFIG,
                    Constants.DB_USERNAME,
                    Constants.DB_PASSWORD);
        } catch (Exception e) {
            logger.error("数据库连接失败", e);
            throw new RuntimeException(e);
        }
        PreparedStatement ps = null;
        ResultSet tableResults = null;
        try {
            ps = connection.prepareStatement("show table status");
            tableResults = ps.executeQuery();  // 获取查询结果
            List<TableInfo> tableInfoList = new ArrayList<>();   // 数据表信息字段
            //读取表
            while (tableResults.next()) {
                String tableName = tableResults.getString("name");  // 数据表名字
                String tableComment = tableResults.getString("comment");  // 数据表的备注
                String beanName = tableName;  // 数据表的实体类名字

                // 如果数据表名是以tb_开头的 并且 设置为忽略前缀 name就将数据表名的前缀tb_删除掉
                if (beanName.startsWith("tb_") && Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(3);
                    while (beanName.startsWith("_")) {  // 判断截取后是否是以_下划线开头的数据表名
                        beanName = beanName.substring(1);
                    }
                    if (beanName.length() < 1) {
                        throw new RuntimeException("数据表 " + tableName + " 名称设置错误 请修改为 XXXXX 或者 XXXXX_XXXXX");
                    }
                }

                beanName = processField(true, beanName);  // 将首字母大写  转换位java命名规则

                TableInfo tableInfo = new TableInfo();  // 数据表的信息
                tableInfo.setTableName(tableName);  // 数据表名
                tableInfo.setComment(tableComment);  // 数据表备注
                tableInfo.setBeanName(beanName);  // 数据表的实体类的类名

                //获取字段信息
                Map<String, FieldInfo> fieldInfoMap = new HashMap();
                // 读取字段信息
                List<FieldInfo> fieldInfoList = readFieldInfo(tableInfo, fieldInfoMap);

                tableInfo.setFieldList(fieldInfoList);  // 设置字段信息集合

                //读取主键、索引
                getKeyIndexInfo(tableInfo, fieldInfoMap);

                tableInfoList.add(tableInfo);
            }
            return tableInfoList;
        } catch (Exception e) {
            logger.error("获取数据库表失败", e);
            throw new RuntimeException("获取数据库表失败");
        } finally {
            if (null != tableResults) {
                try {
                    tableResults.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取索引 主键
     * @param tableInfo
     * @param fieldInfoMap
     */
    private static void getKeyIndexInfo(TableInfo tableInfo, Map<String, FieldInfo> fieldInfoMap) {
        PreparedStatement ps = null;
        ResultSet results = null;
        try {
            ps = connection.prepareStatement("show index from " + tableInfo.getTableName());
            results = ps.executeQuery();
            //获取索引 主键信息
            while (results.next()) {
                String keyName = results.getString("KEY_NAME");
                int nonUnique = results.getInt("NON_UNIQUE");
                String columnName = results.getString("COLUMN_NAME");
                if (nonUnique == 0) {//unique  唯一索引
                    List<FieldInfo> keyColumnList = tableInfo.getKeyIndexMap().get(keyName);
                    if (null == keyColumnList) {
                        keyColumnList = new ArrayList<>();
                        tableInfo.getKeyIndexMap().put(keyName, keyColumnList);
                    }
                    keyColumnList.add(fieldInfoMap.get(columnName));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != results) {
                try {
                    results.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 读取字段信息
     *
     * @param tableInfo
     * @param fieldInfoMap
     * @return
     */
    private static List<FieldInfo> readFieldInfo(TableInfo tableInfo, Map<String, FieldInfo> fieldInfoMap) {
        PreparedStatement ps = null;
        ResultSet fieldResult = null;
        try {
            ps = connection.prepareStatement("show full fields from " + tableInfo.getTableName());
            fieldResult = ps.executeQuery();  // 执行查询
            List<FieldInfo> filedInfoList = new ArrayList<>();
            while (fieldResult.next()) {

                String field = fieldResult.getString("FIELD");  // 获取字段名称
                String type = fieldResult.getString("TYPE");  // 获取字段类型 SQL 类型
                String extra = fieldResult.getString("EXTRA");  // 该字段是否是自动递增
                String comment = fieldResult.getString("COMMENT"); // 读取字段备注

                // 判断 类型加设置位数没有 如：varchar(255)
                if (type.indexOf("(") > 0) {
                    // 将后面的（255）去掉
                    type = type.substring(0, type.indexOf("("));
                }
                // 转换为java属性的名称 如 user_id => userId
                String propertyName = processField(false, field);
                String javaType = processFieldType(type);  //获取字段java类型 将SQL类型转换为java对应的类型

                //判断是否date类型
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
                    tableInfo.setHaveDate(true);
                }
                //判断是否有datetime类型
                if (ArrayUtils.contains(Constants.SQL_DATE_TIIME_TYPES, type)) {
                    tableInfo.setHaveDateTime(true);
                }
                //判断是否有bigdecimal类型
                if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE, type)) {
                    tableInfo.setHaveBigDecimal(true);
                }

                // 创建字段信息
                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setFieldName(field);  // 设置字段名称
                fieldInfo.setPropertyName(propertyName); // java属性名称
                fieldInfo.setSqlType(type);  // sql类型
                fieldInfo.setJavaType(javaType);  // java类型
                fieldInfo.setComment(comment);  // 字段备注
                if ("auto_increment".equalsIgnoreCase(extra)) {  // 判断是否是自增字段
                    fieldInfo.setAutoIncrement(true);
                } else {
                    fieldInfo.setAutoIncrement(false);
                }
                // logger.info("字段名:{},类型:{}，扩展:{}，备注:{}，Java类型:{},Jave属性名:{}", field, type, extra, comment, javaType, propertyName);

                filedInfoList.add(fieldInfo);
                fieldInfoMap.put(field, fieldInfo);

            }
            return filedInfoList;
        } catch (Exception e) {
            logger.error("读取表属性失败", e);
            throw new RuntimeException("读取表属性失败" + tableInfo.getTableName(), e);
        } finally {
            if (fieldResult != null) {
                try {
                    fieldResult.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static String processField(Boolean uperCaseFirstLetter, String field) {  // 将数据表名或数据表字段名转换位java命名规则
        StringBuffer sb = new StringBuffer(field.length());
        String[] fields = field.toLowerCase().split("_");//
        sb.append(uperCaseFirstLetter ? StringTools.upperCaseFirstLetter(fields[0]) : fields[0]);
        for (int i = 1, len = fields.length; i < len; i++) {
            sb.append(StringTools.upperCaseFirstLetter(fields[i]));
        }
        return sb.toString();
    }

    /**
     * 处理字段的类型
     *
     * @param type SQL类型
     * @return java 类型
     */
    private static String processFieldType(String type) {

        if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPE, type)) {
            return "Integer";
        } else if (ArrayUtils.contains(Constants.SQL_LONG_TYPE, type)) {
            return "Long";
        } else if (ArrayUtils.contains(Constants.SQL_STRING_TYPE, type)) {
            return "String";
        } else if (ArrayUtils.contains(Constants.SQL_DATE_TIIME_TYPES, type) || ArrayUtils.contains(Constants.SQL_DATE_TYPES, type)) {
            return "Date";
        } else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPE, type)) {
            return "Double";
        } else if (ArrayUtils.contains(Constants.SQL_BYTE_TYPE, type)) {
            return "byte[]";
        } else {
            return "Object";
        }
    }

}
